#include "DatabaseManager.h"
#include <QSqlError>
#include <QDateTime>
#include <QDebug>

// 初始化静态成员变量
DatabaseManager* DatabaseManager::s_instance = nullptr;

// 获取单例实例的静态方法
DatabaseManager* DatabaseManager::getInstance() {
    if (!s_instance) {
        s_instance = new DatabaseManager();
    }
    return s_instance;
}

// 释放单例实例的静态方法
void DatabaseManager::destroyInstance() {
    if (s_instance) {
        delete s_instance;
        s_instance = nullptr;
    }
}

// 将构造函数改为私有，防止外部直接创建实例
DatabaseManager::DatabaseManager(QObject *parent) : QObject(parent), m_currentSubjectId(1) {
}

DatabaseManager::~DatabaseManager() {
    if (m_db.isOpen()) {
        m_db.close();
    }
}

bool DatabaseManager::init(const QString &dbFile) {
    // 确保使用明确的连接名称，而不是默认连接
    m_db = QSqlDatabase::addDatabase("QSQLITE", "MainConnection");
    m_db.setDatabaseName(dbFile);
    if (!m_db.open()) {
        qWarning() << "无法打开数据库:" << m_db.lastError().text();
        return false;
    }
    // 创建数据表（如不存在）
    if (!createTables()) {
        qWarning() << "数据库表创建失败:" << m_db.lastError().text();
        return false;
    }
    // 初始化时加载被试列表
    emit subjectListChanged(getAllSubjects());
    return true;
}

bool DatabaseManager::createTables() {
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    // 被试信息表
    query.exec("CREATE TABLE IF NOT EXISTS subjects ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "name TEXT, gender TEXT, age INTEGER, timestamp INTEGER)");

    // 被试选填信息表
    query.exec("CREATE TABLE IF NOT EXISTS subject_optional_data ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "subject_id INTEGER,"
               "category TEXT,"
               "field_name TEXT,"
               "field_value TEXT,"
               "FOREIGN KEY(subject_id) REFERENCES subjects(id))");

    // GSR数据表
    query.exec("CREATE TABLE IF NOT EXISTS gsr_data ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "timestamp INTEGER, value1 REAL, value2 REAL, subject_id INTEGER)");

    // EEG数据表（三通道：FP1, FPZ, FP2）
    query.exec("CREATE TABLE IF NOT EXISTS eeg_data ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "timestamp INTEGER, fp1 REAL, fpz REAL, fp2 REAL, subject_id INTEGER)");
    // EEG原始数据表（三通道：FP1, FPZ, FP2）- 滤波前的数据
    query.exec("CREATE TABLE IF NOT EXISTS eeg_raw_data ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "timestamp INTEGER, fp1 REAL, fpz REAL, fp2 REAL, subject_id INTEGER,"
               "filtered_data_id INTEGER,"  // 关联到滤波后数据的ID
               "FOREIGN KEY(filtered_data_id) REFERENCES eeg_data(id))");
    // ECG数据表（八通道）
    query.exec("CREATE TABLE IF NOT EXISTS ecg_data ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "timestamp INTEGER, subject_id INTEGER,"
               "ch1 REAL, ch2 REAL, ch3 REAL, ch4 REAL,"
               "ch5 REAL, ch6 REAL, ch7 REAL, ch8 REAL)");
    // ECG原始数据表（八通道）- 滤波前的数据
    query.exec("CREATE TABLE IF NOT EXISTS ecg_raw_data ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "timestamp INTEGER, subject_id INTEGER,"
               "ch1 REAL, ch2 REAL, ch3 REAL, ch4 REAL,"
               "ch5 REAL, ch6 REAL, ch7 REAL, ch8 REAL,"
               "filtered_data_id INTEGER,"  // 关联到滤波后数据的ID
               "FOREIGN KEY(filtered_data_id) REFERENCES ecg_data(id))");

    // 日志事件表（记录聊天、音频、训练等事件）
    query.exec("CREATE TABLE IF NOT EXISTS events ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "timestamp INTEGER, type TEXT, detail TEXT, role TEXT, subject_id INTEGER)");

    return true;
}

QList<QVariantList> DatabaseManager::getAllSubjects() {
    QList<QVariantList> list;
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    query.exec("SELECT id, name, gender, age FROM subjects");
    while (query.next()) {
        QVariantList row;
        row << query.value(0) << query.value(1) << query.value(2) << query.value(3);
        list.append(row);
    }
    return list;
}

void DatabaseManager::addSubject(const QString &name, const QString &gender, int age, const QMap<QString, QString> &optionalData) {
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接

    // 开始事务
    m_db.transaction();

    // 插入基本信息
    query.prepare("INSERT INTO subjects (name, gender, age, timestamp) VALUES (?, ?, ?, ?)");
    query.addBindValue(name);
    query.addBindValue(gender);
    query.addBindValue(age);
    query.addBindValue(QDateTime::currentSecsSinceEpoch());

    if (!query.exec()) {
        qWarning() << "插入被试失败:" << query.lastError().text();
        m_db.rollback();
        return;
    }

    // 获取新插入的被试ID
    int subjectId = query.lastInsertId().toInt();

    // 如果有选填数据，需要创建并使用subject_optional_data表
    if (!optionalData.isEmpty()) {
        // 确保表存在
        QSqlQuery tableQuery(m_db);
        tableQuery.exec("CREATE TABLE IF NOT EXISTS subject_optional_data ("
                        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                        "subject_id INTEGER,"
                        "category TEXT,"
                        "field_name TEXT,"
                        "field_value TEXT,"
                        "FOREIGN KEY(subject_id) REFERENCES subjects(id))");

        // 插入选填数据
        bool allOptionalDataInserted = true;

        for (auto it = optionalData.constBegin(); it != optionalData.constEnd(); ++it) {
            QString key = it.key();
            QString value = it.value();

            // 解析类别和字段名
            QStringList parts = key.split(":");
            if (parts.size() == 2) {
                QString category = parts[0];
                QString fieldName = parts[1];

                query.prepare("INSERT INTO subject_optional_data (subject_id, category, field_name, field_value) "
                              "VALUES (?, ?, ?, ?)");
                query.addBindValue(subjectId);
                query.addBindValue(category);
                query.addBindValue(fieldName);
                query.addBindValue(value);

                if (!query.exec()) {
                    qWarning() << "插入选填数据失败:" << query.lastError().text();
                    allOptionalDataInserted = false;
                    // 不中断循环，继续尝试插入其他数据
                }
            }
        }

        // 如果有任何选填数据插入失败，记录警告但不回滚事务
        if (!allOptionalDataInserted) {
            qWarning() << "部分选填数据插入失败，但基本信息已成功插入";
        }
    }

    // 提交事务
    if (!m_db.commit()) {
        qWarning() << "提交事务失败:" << m_db.lastError().text();
        m_db.rollback();
        return;
    }

    // 设置当前被试ID
    setCurrentSubjectId(subjectId);

    // 插入成功，发出列表更新信号
    emit subjectListChanged(getAllSubjects());
}

void DatabaseManager::insertGsrData(double value1, double value2, qint64 timestamp) {
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    query.prepare("INSERT INTO gsr_data (timestamp, value1, value2, subject_id) VALUES (?, ?, ?, ?)");
    query.addBindValue(timestamp);
    query.addBindValue(value1);
    query.addBindValue(value2);
    query.addBindValue(m_currentSubjectId);
    if (!query.exec()) {
        qWarning() << "插入GSR数据失败:" << query.lastError().text();
    }
}

int DatabaseManager::insertEegData(double fp1, double fpz, double fp2, qint64 timestamp) {
    // 使用正确的数据库连接
    QSqlQuery query(m_db);
    query.prepare("INSERT INTO eeg_data (timestamp, fp1, fpz, fp2, subject_id) VALUES (?, ?, ?, ?, ?)");
    query.addBindValue(timestamp);
    query.addBindValue(fp1);
    query.addBindValue(fpz);
    query.addBindValue(fp2);
    query.addBindValue(m_currentSubjectId);

    if (!query.exec()) {
        qWarning() << "插入EEG数据失败:" << query.lastError().text();
        return -1;
    }

    // 返回插入的记录ID，用于关联原始数据
    return query.lastInsertId().toInt();
}

void DatabaseManager::insertEegRawData(double fp1, double fpz, double fp2, qint64 timestamp, int filteredDataId) {
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    query.prepare("INSERT INTO eeg_raw_data (timestamp, fp1, fpz, fp2, subject_id, filtered_data_id) VALUES (?, ?, ?, ?, ?, ?)");
    query.addBindValue(timestamp);
    query.addBindValue(fp1);
    query.addBindValue(fpz);
    query.addBindValue(fp2);
    query.addBindValue(m_currentSubjectId);
    query.addBindValue(filteredDataId);
    if (!query.exec()) {
        qWarning() << "插入EEG原始数据失败:" << query.lastError().text();
    }
}

// 修改insertEcgData方法，使用正确的数据库连接
int DatabaseManager::insertEcgData(const QVector<double> &values, int channelIndex, qint64 timestamp) {
    // 如果未提供时间戳，使用当前时间
    if (timestamp == 0) {
        timestamp = QDateTime::currentMSecsSinceEpoch();
    }

    // 静态计数器，用于控制写入频率
    static int writeCounter = 0;
    static const int WRITE_INTERVAL = 10;  // 每10个数据包写入一次数据库

    // 静态变量，保存每个通道的最后一帧有效数据
    static QVector<double> lastChannelValues(8, 0.0);

    // 如果是所有通道数据(-1)或者是指定间隔，则写入数据库
    if (channelIndex == -1 || (++writeCounter % WRITE_INTERVAL == 0)) {
        QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接

        // 构建插入语句
        QString insertSql = "INSERT INTO ecg_data (timestamp, subject_id, ch1, ch2, ch3, ch4, ch5, ch6, ch7, ch8) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        query.prepare(insertSql);
        query.addBindValue(timestamp);
        query.addBindValue(m_currentSubjectId);

        // 处理所有通道数据的情况
        if (channelIndex == -1 && values.size() == 8) {
            // 直接插入所有通道数据，并更新lastChannelValues
            for (int i = 0; i < 8; i++) {
                double value = values[i];
                // 如果值有效，更新lastChannelValues
                if (std::abs(value) > 0.0001) {
                    lastChannelValues[i] = value;
                }
                query.addBindValue(value);
            }
        } else {
            // 处理单通道数据的情况
            for (int i = 0; i < 8; i++) {
                if (i == channelIndex && !values.isEmpty()) {
                    double value = values.last();  // 使用最新的值
                    // 如果值有效，更新lastChannelValues
                    if (std::abs(value) > 0.0001) {
                        lastChannelValues[i] = value;
                    }
                    query.addBindValue(value);
                } else {
                    // 使用该通道的上一帧数据而不是固定值
                    // 添加小的随机波动使曲线更自然
                    double randomOffset = (rand() % 100 - 50) / 5000.0;  // 小的随机波动
                    query.addBindValue(lastChannelValues[i] + randomOffset);
                }
            }
        }

        if (!query.exec()) {
            qWarning() << "插入ECG数据失败:" << query.lastError().text();
            return -1;
        }
        // 返回插入的记录ID，用于关联原始数据
        return query.lastInsertId().toInt();
    }
    return -1;
}

void DatabaseManager::insertEcgRawData(const QVector<double> &values, qint64 timestamp, int filteredDataId) {
    // 如果未提供时间戳，使用当前时间
    if (timestamp == 0) {
        timestamp = QDateTime::currentMSecsSinceEpoch();
    }

    // 如果filteredDataId无效，不执行插入
    if (filteredDataId <= 0) {
        return;
    }

    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接

    // 构建插入语句
    QString insertSql = "INSERT INTO ecg_raw_data (timestamp, subject_id, ch1, ch2, ch3, ch4, ch5, ch6, ch7, ch8, filtered_data_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    query.prepare(insertSql);
    query.addBindValue(timestamp);
    query.addBindValue(m_currentSubjectId);

    // 添加所有通道数据
    for (int i = 0; i < 8; i++) {
        if (i < values.size()) {
            query.addBindValue(values[i]);
        } else {
            query.addBindValue(0.0); // 如果没有数据，插入0
        }
    }

    // 添加关联ID
    query.addBindValue(filteredDataId);

    if (!query.exec()) {
        qWarning() << "插入ECG原始数据失败:" << query.lastError().text();
    }
}

void DatabaseManager::logChatEvent(const QString &message, const QString &role) {
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    // 检查events表是否包含role字段
    QSqlQuery checkQuery("PRAGMA table_info(events)", m_db); // 使用类成员m_db
    bool hasRoleColumn = false;
    bool hasSubjectIdColumn = false;

    while (checkQuery.next()) {
        QString columnName = checkQuery.value(1).toString();
        if (columnName == "role") {
            hasRoleColumn = true;
        }
        if (columnName == "subject_id") {
            hasSubjectIdColumn = true;
        }
    }

    // 如果表结构不匹配，先添加缺少的列
    if (!hasRoleColumn) {
        QSqlQuery alterQuery("ALTER TABLE events ADD COLUMN role TEXT", m_db);
        alterQuery.exec();
    }

    if (!hasSubjectIdColumn) {
        QSqlQuery alterQuery("ALTER TABLE events ADD COLUMN subject_id INTEGER", m_db);
        alterQuery.exec();
    }

    // 根据表结构构建正确的SQL语句
    if (hasRoleColumn && hasSubjectIdColumn) {
        query.prepare("INSERT INTO events (timestamp, type, detail, role, subject_id) VALUES (?, ?, ?, ?, ?)");
        query.addBindValue(QDateTime::currentSecsSinceEpoch());
        query.addBindValue("chat");
        query.addBindValue(message);
        query.addBindValue(role);
        query.addBindValue(m_currentSubjectId);
    } else {
        // 兼容旧表结构
        query.prepare("INSERT INTO events (timestamp, type, detail) VALUES (?, ?, ?)");
        query.addBindValue(QDateTime::currentSecsSinceEpoch());
        query.addBindValue("chat");
        query.addBindValue(message);
    }

    if (!query.exec()) {
        qWarning() << "插入聊天记录失败:" << query.lastError().text();
    }
}

// 修改logAudioEvent方法，使用正确的数据库连接
void DatabaseManager::logAudioEvent(const QString &event, qint64 timestamp) {
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    query.prepare("INSERT INTO events (timestamp, type, detail, subject_id) VALUES (?, ?, ?, ?)");
    query.addBindValue(timestamp / 1000); // 转换ms为秒
    query.addBindValue("audio");
    query.addBindValue(event);
    query.addBindValue(m_currentSubjectId);

    if (!query.exec()) {
        qWarning() << "插入音频事件失败:" << query.lastError().text();
    }
}

// 修改logAnalysisEvent方法，使用正确的数据库连接
void DatabaseManager::logAnalysisEvent(const QString &params) {
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    query.prepare("INSERT INTO events (timestamp, type, detail, subject_id) VALUES (?, ?, ?, ?)");
    query.addBindValue(QDateTime::currentSecsSinceEpoch());
    query.addBindValue("analysis");
    query.addBindValue(params);
    query.addBindValue(m_currentSubjectId);

    if (!query.exec()) {
        qWarning() << "插入分析事件失败:" << query.lastError().text();
    }
}

QList<QPair<QString, QString>> DatabaseManager::getChatHistory(int limit) {
    QList<QPair<QString, QString>> history;
    QSqlQuery query(m_db); // 使用类成员m_db而不是默认连接
    query.prepare("SELECT detail, role FROM events WHERE type = 'chat' AND subject_id = ? "
                  "ORDER BY timestamp DESC LIMIT ?");
    query.addBindValue(m_currentSubjectId);
    query.addBindValue(limit);

    if (query.exec()) {
        while (query.next()) {
            QString message = query.value(0).toString();
            QString role = query.value(1).toString();
            history.append(qMakePair(role, message));
        }
    } else {
        qWarning() << "获取聊天历史失败:" << query.lastError().text();
    }

    // 反转列表，使其按时间顺序排列
    std::reverse(history.begin(), history.end());
    return history;
}
